package com.zlj.jdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

/**
 * PreparedStatement
 * 可以防止SQL注入(SQL攻击)
 * 
 * @author bashen
 *
 */
public class PreparedStatementDemo {
    public boolean login(String username, String password) throws ClassNotFoundException, SQLException {
    	Class.forName("com.mysql.jdbc.Driver");
    	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testsql", "root", "tiger");
    	Statement statement = conn.createStatement();
    	String sql = "select * from t_user where username = '" + username +"' and password = '" + password + "'";
    	System.out.println(sql);
    	ResultSet resultSet = statement.executeQuery(sql);
    	return resultSet.next();
    }
	
    @Test
    public void test01() throws Exception {
    	//sql = select * from t_user where username = 'a' or 'a' = 'a' and password = 'a' or 'a' = 'a'
    	String username = "a' or 'a' = 'a";
    	String password = "a' or 'a' = 'a";
    	System.out.println(login(username,password));  //结果为true,可是数据库中根本就么有这数据，这就叫做SQL攻击(SQL注入)
    }
    
    public boolean login2(String username, String password) throws ClassNotFoundException, SQLException {
    	Class.forName("com.mysql.jdbc.Driver");
    	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testsql", "root", "tiger");
    	/*
    	 * 一、得到PreparedStatement
    	 * 1、给出sql模板：所有的参数使用？代替;
    	 * 2、调用Connection方法，得到PreparedStatement;
    	 */
    	String sql = "select * from t_user where username = ? and password = ?";
    	PreparedStatement statement = conn.prepareStatement(sql);
    	//为参数赋值
    	statement.setString(1, username); //给第一个？赋值，值为username
    	statement.setString(2, username); //给第二个？赋值，值为password
    	
    	//调用查询方法，向数据库发送查询语句
    	ResultSet resultSet2 = statement.executeQuery();
    	return resultSet2.next();
    }
    
    @Test
    public void test02() throws Exception {
    	//sql = select * from t_user where username = 'a' or 'a' = 'a' and password = 'a' or 'a' = 'a'
    	String username = "a' or 'a' = 'a";
    	String password = "a' or 'a' = 'a";
    	System.out.println(login2(username,password));  //结果为false,有效的防止了SQL攻击(SQL注入)
    }
}
